package com.scalar.chiptrack.finance.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.CostCode;
import com.scalar.chiptrack.finance.ProcessStepCostInfo;
import com.scalar.chiptrack.operations.process.ChiptrackProduct;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import org.apache.struts.util.LabelValueBean;

import java.sql.*;
import java.util.ArrayList;

public final class UpdateViewCostDAO {


    public static void updateProcessStepCost ( ArrayList<ProcessStepCostInfo> processStepCostInfo, int year, String costType ) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        if (processStepCostInfo == null) {
            return;
        }

        try {
            conn = DataSource.getConnection();
			cstmt = conn.prepareCall( SqlQueryMappings.get( "finance.updateviewcost.insertupdateProcessStepCost" ) );
            ProcessStepCostInfo psCostInfo = null;
			for ( int costCount = 0; costCount < processStepCostInfo.size(); costCount++ )
			{
                psCostInfo = (ProcessStepCostInfo) processStepCostInfo.get( costCount );
                cstmt.setInt( 1, psCostInfo.getProcessStepId());

                cstmt.setString ( 2, psCostInfo.getQ1StandardCost().trim() );
				cstmt.setString ( 3, psCostInfo.getQ1Yield().trim() );

				cstmt.setString ( 4, psCostInfo.getQ2StandardCost().trim() );
				cstmt.setString ( 5, psCostInfo.getQ2Yield().trim() );

				cstmt.setString ( 6, psCostInfo.getQ3StandardCost().trim() );
				cstmt.setString ( 7, psCostInfo.getQ3Yield().trim() );

				cstmt.setString ( 8, psCostInfo.getQ4StandardCost().trim() );
				cstmt.setString ( 9, psCostInfo.getQ4Yield().trim() );
                cstmt.setString ( 10, psCostInfo.getProjectedCost().trim() );
				cstmt.setString ( 11, psCostInfo.getProjectedYield().trim() );
				cstmt.setInt 	( 12, year );
				cstmt.setString ( 13, costType );

                cstmt.addBatch();
                cstmt.clearParameters();
            }
            cstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not update ProcessStep cost information", sqle);
        }

		finally
		{
			try
			{
				if (conn != null)
				{
					if (success == true)
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
			}
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

    public static void updateProcessStepCostByLocation ( ArrayList<ProcessStepCostInfo> processStepCostInfo, int year , String costType ) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        if (processStepCostInfo == null) {
            return;
        }

        try {
            conn = DataSource.getConnection();
			cstmt = conn.prepareCall( "{Call sp_create_update_view_cost_by_location(?,?,?,?,?,?,?,?,?,?,?,?,?,?) }" );
            ProcessStepCostInfo psCostInfo = null;
			for ( int costCount = 0; costCount < processStepCostInfo.size(); costCount++ )
			{
                psCostInfo = (ProcessStepCostInfo) processStepCostInfo.get( costCount );
                cstmt.setInt( 1, psCostInfo.getProcessStepId() );
                cstmt.setInt( 2, psCostInfo.getLocationId() );
                
                cstmt.setString ( 3, psCostInfo.getQ1StandardCost().trim() );
				cstmt.setString ( 4, psCostInfo.getQ1Yield().trim() );

				cstmt.setString ( 5, psCostInfo.getQ2StandardCost().trim() );
				cstmt.setString ( 6, psCostInfo.getQ2Yield().trim() );

				cstmt.setString ( 7, psCostInfo.getQ3StandardCost().trim() );
				cstmt.setString ( 8, psCostInfo.getQ3Yield().trim() );

				cstmt.setString ( 9, psCostInfo.getQ4StandardCost().trim() );
				cstmt.setString ( 10, psCostInfo.getQ4Yield().trim() );
                cstmt.setString ( 11, psCostInfo.getProjectedCost().trim() );
				cstmt.setString ( 12, psCostInfo.getProjectedYield().trim() );

				cstmt.setInt 	( 13, year );
				cstmt.setString ( 14, costType );

                cstmt.addBatch();
                cstmt.clearParameters();
            }
            cstmt.executeBatch();
            success = true;
        }

        catch (Exception sqle)
       	{
            throw new ScalarException ("Could not update ProcessStep cost information", sqle);
        }

		finally
		{
			try
			{
				if (conn != null)
				{
					if (success == true)
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
			}
			catch (SQLException ignore) {}

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
	}

	public static ArrayList<ProcessStepCostInfo> loadProcessStepCostByProductId ( int productId, int year ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<ProcessStepCostInfo> processStepCostList = new ArrayList<ProcessStepCostInfo>();

		if (productId == -1)
		{
			return processStepCostList;
		}
        String decimalPatteran = ConfigManager.getDecmalsInAmountPattern();
		try
		{
			conn = DataSource.getConnection();

            ps = conn.prepareStatement ( SqlQueryMappings.get ( "finance.updateviewcost.loadProcessStepsFlowByProductId" ) );
			ps.setInt ( 1, productId );
			ps.setInt ( 2, year );
			rs = ps.executeQuery();
			String psType = null;

			while ( rs.next() )
			{
				psType = rs.getString ("PS_Type").trim();

				if (processStepCostList == null)
				{
					processStepCostList = new ArrayList<ProcessStepCostInfo>();
				}
				ProcessStepCostInfo processStepCostInfo = new ProcessStepCostInfo ();

				processStepCostInfo.setProcessStepId( rs.getInt ( "ProcessStep_ID" ) );
				processStepCostInfo.setProcessStepType( psType );
                processStepCostInfo.setPartNumber( rs.getString ( "Part_Number" ) );

                processStepCostInfo.setQ1StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q1_Standard_Cost" )).doubleValue()), "###0"+decimalPatteran ) );
                processStepCostInfo.setQ1Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q1_Yield" )).doubleValue()), "###.###") );
                processStepCostInfo.setQ2StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q2_Standard_Cost" )).doubleValue()), "###0"+decimalPatteran ) );
                processStepCostInfo.setQ2Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q2_Yield" )).doubleValue()), "###.###") );

                processStepCostInfo.setQ3StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q3_Standard_Cost" )).doubleValue()), "###0"+decimalPatteran ) );
                processStepCostInfo.setQ3Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q3_Yield" )).doubleValue()), "###.###") );

                processStepCostInfo.setQ4StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q4_Standard_Cost" )).doubleValue()), "###0"+decimalPatteran) );
                processStepCostInfo.setQ4Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q4_Yield" )).doubleValue()), "###.###") );
                processStepCostInfo.setProjectedCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Projected_Cost" )).doubleValue()), "###0"+decimalPatteran) );
                processStepCostInfo.setProjectedYield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Projected_Yield" )).doubleValue()), "###.###") );

                processStepCostList.add ( processStepCostInfo );
            }
        }

		catch (SQLException se)
		{
		    throw new ScalarException ("Could not load process step cost information for product ID: " + productId, se);
		}

		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return processStepCostList;
    }

	public static ArrayList<ProcessStepCostInfo> loadProcessStepCostByProductIdLocation ( int productId, int year ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<ProcessStepCostInfo> processStepCostList = new ArrayList<ProcessStepCostInfo>();

		if (productId == -1)
		{
			return processStepCostList;
		}
        String decimalPatteran = ConfigManager.getDecmalsInAmountPattern();
		try
		{
			conn = DataSource.getConnection();

            ps = conn.prepareStatement ( "select * from fn_update_view_cost_by_flow_and_location (?, ?) order by Process_Name_ID, PS_Type, Part_Number" );
			ps.setInt ( 1, productId );
			ps.setInt ( 2, year );
			rs = ps.executeQuery();
			String psType = null;

			while ( rs.next() )
			{
				psType = rs.getString ("PS_Type").trim();

				if (processStepCostList == null)
				{
					processStepCostList = new ArrayList<ProcessStepCostInfo>();
				}
				ProcessStepCostInfo processStepCostInfo = new ProcessStepCostInfo ();

				processStepCostInfo.setProcessStepId( rs.getInt ( "ProcessStep_ID" ) );
				processStepCostInfo.setProcessStepType( psType );
				processStepCostInfo.setLocationId( rs.getInt( "Location_ID" ) );
				processStepCostInfo.setLocation( rs.getString( "Location" ) );
                processStepCostInfo.setPartNumber( rs.getString ( "Part_Number" ) );

                processStepCostInfo.setQ1StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q1_Standard_Cost" )).doubleValue()), "###0"+ decimalPatteran ) );
                processStepCostInfo.setQ1Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q1_Yield" )).doubleValue()), "###.###") );
                processStepCostInfo.setQ2StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q2_Standard_Cost" )).doubleValue()), "###0"+ decimalPatteran ) );
                processStepCostInfo.setQ2Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q2_Yield" )).doubleValue()), "###.###") );

                processStepCostInfo.setQ3StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q3_Standard_Cost" )).doubleValue()), "###0"+ decimalPatteran ) );
                processStepCostInfo.setQ3Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q3_Yield" )).doubleValue()), "###.###") );

                processStepCostInfo.setQ4StandardCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q4_Standard_Cost" )).doubleValue()), "###0"+ decimalPatteran ) );
                processStepCostInfo.setQ4Yield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Q4_Yield" )).doubleValue()), "###.###") );
                processStepCostInfo.setProjectedCost( StringUtils.formatAmount((float)(new Double(rs.getString ( "Projected_Cost" )).doubleValue()),    "###0"+ decimalPatteran ) );
                processStepCostInfo.setProjectedYield( StringUtils.formatAmount((float)(new Double(rs.getString ( "Projected_Yield" )).doubleValue()), "###.###") );

                processStepCostList.add ( processStepCostInfo );
            }
        }

		catch (SQLException se)
		{
		    throw new ScalarException ("Could not load process step cost information for product ID: " + productId, se);
		}

		finally
		{
			if (rs != null)
			{
			    try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null)
			{
			    try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null)
			{
			    try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return processStepCostList;
    }

    public static ArrayList<ChiptrackProduct> getProductNameList( DBTransaction dbTransaction, int productId ) throws ScalarException
    {
        Connection conn  = null;
        ResultSet rs     = null;
        PreparedStatement pstmt = null;
        ArrayList<ChiptrackProduct> list = new ArrayList<ChiptrackProduct>();

        try
        {

            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ("finance.updateviewcost.loadPartNumberPTypeByProductId") );
            pstmt.setInt ( 1 , productId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                list.add ( new ChiptrackProduct ( rs.getInt("Product_ID")  , rs.getString("Part_Number") ) );
            }

        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Error while getting the Product Name ", sql);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return list;
    }

    public static void calculateCost( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt	= null;
        boolean success         = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( ConfigManager.isShowUpdateViewCostByLocation() == false )
            {
                if (ConfigManager.isCalculateCostByCummulative() )
                {
                    cstmt = conn.prepareCall( "{ CALL SP_CALCULATE_CUMMULATIVE_COST_BYPRODUCT() }" );
                }
                else
                {
                    cstmt = conn.prepareCall( "{ CALL SP_CALCULATE_STEP_COST_BYPRODUCT() }" );
                }
                cstmt.executeUpdate();
            }

            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not calculate Cost");
        }
        finally
        {
            try
			{
				if( dbTransaction == null )
				{
					if ( conn != null )
                    {
						if ( success == true )
                        {
							conn.commit();
						}
						else
                        {
							conn.rollback();
						}
					}
				}
            }
            catch (SQLException ex)
			{
				ex.printStackTrace();
			}
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static void updateCostTable ( DBTransaction dbTransaction ) throws ScalarException
        {
            Connection conn     = null;
            CallableStatement cstmt = null;
            boolean success     = false;

            try
            {
                if ( dbTransaction != null )
                {
                    conn = dbTransaction.getConnection();
                }
                else
                {
                    conn = DataSource.getConnection();
                }

                cstmt = conn.prepareCall( "{ CALL SP_UPDATE_COST_TABLE }" );
                cstmt.execute();
                success = true;
            }
            catch( Exception e )
            {
                e.printStackTrace();
                throw new ScalarException ( "Could not update Cost Table", e );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        try
                        {
                            if ( success )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                        catch( SQLException e ) { }
                    }
                }
                if ( cstmt != null)
                {
                    try { cstmt.close(); } catch( SQLException ignore ) { }
                }
                if ( conn != null )
                {
                    try{ conn.close(); } catch( SQLException ignore ) { }
                }
            }
        }

    public static ArrayList loadCostDetailsByProductId ( int productId, int year, String costType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList reportList = new ArrayList();

        if (productId == -1)
        {
            return reportList;
        }

        try
        {
            conn = DataSource.getConnection();
            if ( "Standard".equalsIgnoreCase( costType ) )
            {
                ps = conn.prepareStatement ( "select BasePart_Number As [Base Product], Part_Number As [Part Number], Ps_Type as [Process] \n" +
                        ",(select Parameter_Value from PRocess_parameter WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = FN.PROCESSSTEP_ID) As [Std Yield %]\n" +
                        ",Q1_Standard_Cost as [Q1 Std Cost]\n" +
                        ",Q2_Standard_Cost as [Q2 Std Cost]\n" +
                        ",Q3_Standard_Cost as [Q3 Std Cost]\n" +
                        ",Q4_Standard_Cost as [Q4 Std Cost] from fn_update_view_cost_by_flow ( ?, ? ) FN ORDER BY PROCESS_NAME_ID");
            }
            else
            {
                ps = conn.prepareStatement ( "select BasePart_Number As [Base Product], Part_Number As [Part Number], Ps_Type as [Process] \n" +
                        ",Projected_Cost as [Projected Cost]\n" +
                        ",Projected_Yield as [Projected Yield]\n" +
                        " from fn_update_view_cost_by_flow ( ?, ? ) FN ORDER BY PROCESS_NAME_ID");
            }
            ps.setInt ( 1, productId );
            ps.setInt ( 2, year );
            rs    = ps.executeQuery();

            reportList = ExcelUtils.getAllResultSetRows( rs, true );
        }
        catch (Exception se)
        {
            throw new ScalarException ("Could not load process step cost information for product ID: " + productId, se);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null)
            {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return reportList;
    }
    public static ArrayList loadCostDetailsByProductIdByLocation ( int productId, int year, String costType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList reportList = new ArrayList();

        if (productId == -1)
        {
            return reportList;
        }

        try
        {
            conn = DataSource.getConnection();
            if ( "Standard".equalsIgnoreCase( costType ) )
            {
                ps = conn.prepareStatement ( "select BasePart_Number As [Base Product], Part_Number As [Part Number], Ps_Type as [Process], Location \n" +
                        ",(select Parameter_Value from PRocess_parameter WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = FN.PROCESSSTEP_ID) As [Std Yield %]\n" +
                        ",Q1_Standard_Cost as [Q1 Std Cost]\n" +
                        ",Q2_Standard_Cost as [Q2 Std Cost]\n" +
                        ",Q3_Standard_Cost as [Q3 Std Cost]\n" +
                        ",Q4_Standard_Cost as [Q4 Std Cost] from fn_update_view_cost_by_flow_and_location ( ?, ? ) FN ORDER BY PROCESS_NAME_ID");
            }
            else
            {
                ps = conn.prepareStatement ( "select BasePart_Number As [Base Product], Part_Number As [Part Number], Ps_Type as [Process], Location \n" +
                        ",Projected_Cost as [Projected Cost]\n" +
                        ",Projected_Yield as [Projected Yield]\n" +
                        " from fn_update_view_cost_by_flow_and_location ( ?, ? ) FN ORDER BY PROCESS_NAME_ID");

            }
            ps.setInt ( 1, productId );
            ps.setInt ( 2, year );
            rs    = ps.executeQuery();

            reportList = ExcelUtils.getAllResultSetRows( rs, true );
        }
        catch (Exception se)
        {
            throw new ScalarException ("Could not load process step cost information for product ID: " + productId, se);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null)
            {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return reportList;
    }
    public static ArrayList<LabelValueBean> loadPackages( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> packageList    = null;

        try
        {
            packageList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PROCESS_PARAMETER_VALUE_ID, PROCESS_PARAMETER_VALUE FROM PROCESS_PARAMETER_VALUES WHERE PROCESS_TYPE='ASSEMBLY' AND PROCESS_PARAMETER='PACKAGE ID' AND PROCESS_PARAMETER_VALUE IS NOT NULL AND LEN(PROCESS_PARAMETER_VALUE) > 0 " );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                packageList.add( new LabelValueBean( rs.getString( "PROCESS_PARAMETER_VALUE" ), rs.getString( "PROCESS_PARAMETER_VALUE" ) ) );
            }

        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Package Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return packageList;
    }

    public static ArrayList<LabelValueBean> loadPins ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> pinList    = null;

        try
        {
            pinList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PROCESS_PARAMETER_VALUE_ID, PROCESS_PARAMETER_VALUE FROM PROCESS_PARAMETER_VALUES WHERE PROCESS_TYPE='ASSEMBLY' AND PROCESS_PARAMETER='PIN' AND PROCESS_PARAMETER_VALUE IS NOT NULL " );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                pinList.add( new LabelValueBean( rs.getString( "PROCESS_PARAMETER_VALUE" ), rs.getString( "PROCESS_PARAMETER_VALUE" ) ) );
            }

        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Pin Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return pinList;
    }
    public static ArrayList<LabelValueBean> loadCostCodes ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<LabelValueBean> costCodeList    = null;

        try
        {
            costCodeList = new ArrayList<LabelValueBean>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CC_ID, COST_CODE FROM COST_CODE_TABLE WHERE ASSM_COST>0" );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                costCodeList.add( new LabelValueBean( rs.getString( "COST_CODE" ), rs.getString( "COST_CODE" ) ) );
            }

        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Cost Code Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return costCodeList;
    }

    public static void saveCostCodeDetails ( DBTransaction dbTransaction, ArrayList<CostCode> costCodeList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        if (costCodeList == null)
        {
            return;
        }
        try
        {
             if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareStatement( "UPDATE PRODUCT SET COST_CODE = ? WHERE PROCESSSTEP_ID = ?" );
			pstmt = conn.prepareStatement( "UPDATE PRODUCT SET COST_CODE = ? WHERE PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( ? ) )" );
            
			for( int i = 0; i < costCodeList.size() ; i++ )
            {
                CostCode costCode= (CostCode) costCodeList.get(i);
                
				if( costCode.isChecked() )
                {
                    pstmt.setString ( 1, costCode.getCostCode() );
                    pstmt.setInt ( 2, costCode.getProcessStepId() );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            if (dbTransaction == null)
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            throw new ScalarException ( "Could not update cost Code information", sqle );
        }

        finally
        {
            if (dbTransaction==null && conn != null && !success)
            {
                try{conn.rollback();}catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static ArrayList<CostCode> loadPartNumbers( DBTransaction dbTransaction, int processStepId, String packageString, String pinNumber, boolean withoutCostCodeProducts  ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList<CostCode> costCodeList = null;
        try
        {
            costCodeList = new ArrayList<CostCode>();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareCall( "{ call SP_GET_COST_CODE_DETAILS(?,?,?, ?) }" );
            pstmt.setInt (1, processStepId);
            pstmt.setString(2, packageString);
            pstmt.setString(3, pinNumber);
            pstmt.setInt(4, ( withoutCostCodeProducts==true ? 1 : 0));
            rs = pstmt.executeQuery();
            CostCode costCode;
            while ( rs.next() )
            {
              costCode = new CostCode();
              costCode.setProcessStepId(rs.getInt("PROCESSSTEP_ID"));
              costCode.setMarketingPartNumber(rs.getString("PARTNUMBER"));
              costCode.setBaseProduct(rs.getString("BASEPRODUCT"));
              costCode.setPackageType(rs.getString("PACKAGENAME"));
              costCode.setPinType(rs.getString("PINNUMBER"));
              costCode.setCostCode(rs.getString("COSTCODE"));
              costCodeList.add( costCode );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Pin Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return costCodeList ;
    }
    /*public String getRequiredDateField ( DBTransaction dbTransaction, String term ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;
		String dateVal = null;

		if ( term == null )
		{
			term = "year";
		}

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			String sqlQry = "SELECT "+ term + "(GETDATE()) as curr_"+term;

			pstmt = conn.prepareStatement( sqlQry );
			rs = pstmt.executeQuery();
			while ( rs.next() )
			{
				dateVal = rs.getString ("curr_"+term);
			}
			if ( dateVal != null && dateVal.trim().length > 0 )
			{
				//do nothing
			}
			else
			{


		}
		catch(Exception sql)
		{
			sql.printStackTrace();
			throw new ScalarException("Could not get the required Date Value", sql);
		}
		finally
		{
			if(rs != null)
			{
				try{rs.close();}catch(SQLException se){}
			}
			if(pstmt != null)
			{
				try{pstmt.close();}catch(SQLException se){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException se){}
			}
		}
	}*/
    public static void updateCurrentQuarterValues ( DBTransaction dbTransaction, int baseProductId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;

        boolean success         = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "{CALL SP_UPDATE_CURRENT_QTR_COST_VALUES(?)}" );
            pstmt.setInt ( 1, baseProductId );
            pstmt.executeUpdate( );
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not update the current quarter values", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try
                {
                    conn.rollback();
                }
                catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
    }
}